SQL data into Pandas#
Many companies store data in central databases to ensure efficient data management, security, and accessibility.
Structured Query Language (SQL) is the standard language for interacting with these databases, allowing users to extract and manipulate data. In recent years, Python has become a popular tool for data analysis and manipulation due to its flexibility, ease of use, and extensive libraries. Pandas is a powerful Python library for data manipulation, and it offers seamless integration with SQL databases through SQLAlchemy, a popular SQL toolkit for Python.
By using Python, Pandas, and SQLAlchemy, users can access and analyze data stored in SQL databases and perform complex queries and data transformations. In this article, we will explore how to connect to a SQL database, retrieve data using SQL queries, and analyze the data using Pandas.
How To#
import pandas as pd
import sqlalchemy as sql
conn = sql.create_engine("postgresql://scott:tiger@localhost/test")
df = pd.read_sql_table("sales", conn)
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
145 try:
--> 146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3304, in Engine.raw_connection(self)
3283 """Return a "raw" DBAPI connection from the connection pool.
3284
3285 The returned object is a proxied version of the DBAPI
(...)
3302
3303 """
-> 3304 return self.pool.connect()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
442 """Return a DBAPI connection from the pool.
443
444 The connection is instrumented such that when its
(...)
447
448 """
--> 449 return _ConnectionFairy._checkout(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
1262 if not fairy:
-> 1263 fairy = _ConnectionRecord.checkout(pool)
1265 if threadconns is not None:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
711 else:
--> 712 rec = pool._do_get()
714 try:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:180, in QueuePool._do_get(self)
179 with util.safe_reraise():
--> 180 self._dec_overflow()
181 raise
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
176 try:
--> 177 return self._create_connection()
178 except:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
673 if connect:
--> 674 self.__connect()
675 self.finalize_callback = deque()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:901, in _ConnectionRecord.__connect(self)
900 with util.safe_reraise():
--> 901 pool.logger.debug("Error on connect(): %s", e)
902 else:
903 # in SQLAlchemy 1.4 the first_connect event is not used by
904 # the engine, so this will usually not be set
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
897 pool.logger.debug("Created new connection %r", connection)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
641 return connection
--> 643 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:617, in DefaultDialect.connect(self, *cargs, **cparams)
615 def connect(self, *cargs, **cparams):
616 # inherits the docstring from interfaces.Dialect.connect
--> 617 return self.loaded_dbapi.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
Cell In[4], line 1
----> 1 df = pd.read_sql_table("sales", conn)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:336, in read_sql_table(table_name, con, schema, index_col, coerce_float, parse_dates, columns, chunksize, dtype_backend)
333 if dtype_backend is lib.no_default:
334 dtype_backend = "numpy" # type: ignore[assignment]
--> 336 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
337 if not pandas_sql.has_table(table_name):
338 raise ValueError(f"Table {table_name} not found")
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
829 raise ImportError("Using URI string without sqlalchemy installed.")
831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832 return SQLDatabase(con, schema, need_transaction)
834 warnings.warn(
835 "pandas only supports SQLAlchemy connectable (engine/connection) or "
836 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
(...)
839 stacklevel=find_stack_level(),
840 )
841 return SQLiteDatabase(con)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
1537 self.exit_stack.callback(con.dispose)
1538 if isinstance(con, Engine):
-> 1539 con = self.exit_stack.enter_context(con.connect())
1540 if need_transaction and not con.in_transaction():
1541 self.exit_stack.enter_context(con.begin())
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3280, in Engine.connect(self)
3257 def connect(self) -> Connection:
3258 """Return a new :class:`_engine.Connection` object.
3259
3260 The :class:`_engine.Connection` acts as a Python context manager, so
(...)
3277
3278 """
-> 3280 return self._connection_cls(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:148, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
--> 148 Connection._handle_dbapi_exception_noconnection(
149 err, dialect, engine
150 )
151 raise
152 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2444, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
2442 elif should_wrap:
2443 assert sqlalchemy_exception is not None
-> 2444 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
2445 else:
2446 assert exc_info[1] is not None
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
144 if connection is None:
145 try:
--> 146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
148 Connection._handle_dbapi_exception_noconnection(
149 err, dialect, engine
150 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3304, in Engine.raw_connection(self)
3282 def raw_connection(self) -> PoolProxiedConnection:
3283 """Return a "raw" DBAPI connection from the connection pool.
3284
3285 The returned object is a proxied version of the DBAPI
(...)
3302
3303 """
-> 3304 return self.pool.connect()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
441 def connect(self) -> PoolProxiedConnection:
442 """Return a DBAPI connection from the pool.
443
444 The connection is instrumented such that when its
(...)
447
448 """
--> 449 return _ConnectionFairy._checkout(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
1255 @classmethod
1256 def _checkout(
1257 cls,
(...)
1260 fairy: Optional[_ConnectionFairy] = None,
1261 ) -> _ConnectionFairy:
1262 if not fairy:
-> 1263 fairy = _ConnectionRecord.checkout(pool)
1265 if threadconns is not None:
1266 threadconns.current = weakref.ref(fairy)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
710 rec = cast(_ConnectionRecord, pool._do_get())
711 else:
--> 712 rec = pool._do_get()
714 try:
715 dbapi_connection = rec.get_connection()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:180, in QueuePool._do_get(self)
178 except:
179 with util.safe_reraise():
--> 180 self._dec_overflow()
181 raise
182 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
175 if self._inc_overflow():
176 try:
--> 177 return self._create_connection()
178 except:
179 with util.safe_reraise():
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
387 def _create_connection(self) -> ConnectionPoolEntry:
388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
672 self.__pool = pool
673 if connect:
--> 674 self.__connect()
675 self.finalize_callback = deque()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:901, in _ConnectionRecord.__connect(self)
899 except BaseException as e:
900 with util.safe_reraise():
--> 901 pool.logger.debug("Error on connect(): %s", e)
902 else:
903 # in SQLAlchemy 1.4 the first_connect event is not used by
904 # the engine, so this will usually not be set
905 if pool.dispatch.first_connect:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
894 try:
895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
897 pool.logger.debug("Created new connection %r", connection)
898 self.fresh = True
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
640 if connection is not None:
641 return connection
--> 643 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:617, in DefaultDialect.connect(self, *cargs, **cparams)
615 def connect(self, *cargs, **cparams):
616 # inherits the docstring from interfaces.Dialect.connect
--> 617 return self.loaded_dbapi.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)
query = "SELECT * FROM sales"
df = pd.read_sql_query(query, conn)
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
145 try:
--> 146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3304, in Engine.raw_connection(self)
3283 """Return a "raw" DBAPI connection from the connection pool.
3284
3285 The returned object is a proxied version of the DBAPI
(...)
3302
3303 """
-> 3304 return self.pool.connect()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
442 """Return a DBAPI connection from the pool.
443
444 The connection is instrumented such that when its
(...)
447
448 """
--> 449 return _ConnectionFairy._checkout(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
1262 if not fairy:
-> 1263 fairy = _ConnectionRecord.checkout(pool)
1265 if threadconns is not None:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
711 else:
--> 712 rec = pool._do_get()
714 try:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:180, in QueuePool._do_get(self)
179 with util.safe_reraise():
--> 180 self._dec_overflow()
181 raise
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
176 try:
--> 177 return self._create_connection()
178 except:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
673 if connect:
--> 674 self.__connect()
675 self.finalize_callback = deque()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:901, in _ConnectionRecord.__connect(self)
900 with util.safe_reraise():
--> 901 pool.logger.debug("Error on connect(): %s", e)
902 else:
903 # in SQLAlchemy 1.4 the first_connect event is not used by
904 # the engine, so this will usually not be set
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
897 pool.logger.debug("Created new connection %r", connection)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
641 return connection
--> 643 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:617, in DefaultDialect.connect(self, *cargs, **cparams)
615 def connect(self, *cargs, **cparams):
616 # inherits the docstring from interfaces.Dialect.connect
--> 617 return self.loaded_dbapi.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
Cell In[5], line 2
1 query = "SELECT * FROM sales"
----> 2 df = pd.read_sql_query(query, conn)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:468, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
465 if dtype_backend is lib.no_default:
466 dtype_backend = "numpy" # type: ignore[assignment]
--> 468 with pandasSQL_builder(con) as pandas_sql:
469 return pandas_sql.read_query(
470 sql,
471 index_col=index_col,
(...)
477 dtype_backend=dtype_backend,
478 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
829 raise ImportError("Using URI string without sqlalchemy installed.")
831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832 return SQLDatabase(con, schema, need_transaction)
834 warnings.warn(
835 "pandas only supports SQLAlchemy connectable (engine/connection) or "
836 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
(...)
839 stacklevel=find_stack_level(),
840 )
841 return SQLiteDatabase(con)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
1537 self.exit_stack.callback(con.dispose)
1538 if isinstance(con, Engine):
-> 1539 con = self.exit_stack.enter_context(con.connect())
1540 if need_transaction and not con.in_transaction():
1541 self.exit_stack.enter_context(con.begin())
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3280, in Engine.connect(self)
3257 def connect(self) -> Connection:
3258 """Return a new :class:`_engine.Connection` object.
3259
3260 The :class:`_engine.Connection` acts as a Python context manager, so
(...)
3277
3278 """
-> 3280 return self._connection_cls(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:148, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
--> 148 Connection._handle_dbapi_exception_noconnection(
149 err, dialect, engine
150 )
151 raise
152 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2444, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
2442 elif should_wrap:
2443 assert sqlalchemy_exception is not None
-> 2444 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
2445 else:
2446 assert exc_info[1] is not None
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
144 if connection is None:
145 try:
--> 146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
148 Connection._handle_dbapi_exception_noconnection(
149 err, dialect, engine
150 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3304, in Engine.raw_connection(self)
3282 def raw_connection(self) -> PoolProxiedConnection:
3283 """Return a "raw" DBAPI connection from the connection pool.
3284
3285 The returned object is a proxied version of the DBAPI
(...)
3302
3303 """
-> 3304 return self.pool.connect()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
441 def connect(self) -> PoolProxiedConnection:
442 """Return a DBAPI connection from the pool.
443
444 The connection is instrumented such that when its
(...)
447
448 """
--> 449 return _ConnectionFairy._checkout(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
1255 @classmethod
1256 def _checkout(
1257 cls,
(...)
1260 fairy: Optional[_ConnectionFairy] = None,
1261 ) -> _ConnectionFairy:
1262 if not fairy:
-> 1263 fairy = _ConnectionRecord.checkout(pool)
1265 if threadconns is not None:
1266 threadconns.current = weakref.ref(fairy)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
710 rec = cast(_ConnectionRecord, pool._do_get())
711 else:
--> 712 rec = pool._do_get()
714 try:
715 dbapi_connection = rec.get_connection()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:180, in QueuePool._do_get(self)
178 except:
179 with util.safe_reraise():
--> 180 self._dec_overflow()
181 raise
182 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
175 if self._inc_overflow():
176 try:
--> 177 return self._create_connection()
178 except:
179 with util.safe_reraise():
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
387 def _create_connection(self) -> ConnectionPoolEntry:
388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
672 self.__pool = pool
673 if connect:
--> 674 self.__connect()
675 self.finalize_callback = deque()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:901, in _ConnectionRecord.__connect(self)
899 except BaseException as e:
900 with util.safe_reraise():
--> 901 pool.logger.debug("Error on connect(): %s", e)
902 else:
903 # in SQLAlchemy 1.4 the first_connect event is not used by
904 # the engine, so this will usually not be set
905 if pool.dispatch.first_connect:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
894 try:
895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
897 pool.logger.debug("Created new connection %r", connection)
898 self.fresh = True
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
640 if connection is not None:
641 return connection
--> 643 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:617, in DefaultDialect.connect(self, *cargs, **cparams)
615 def connect(self, *cargs, **cparams):
616 # inherits the docstring from interfaces.Dialect.connect
--> 617 return self.loaded_dbapi.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)
query = """SELECT customers, total_spend from sales
WHERE YEAR = 2019
LIMIT 1000
"""
df = pd.read_sql_query(query, conn)
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
145 try:
--> 146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3304, in Engine.raw_connection(self)
3283 """Return a "raw" DBAPI connection from the connection pool.
3284
3285 The returned object is a proxied version of the DBAPI
(...)
3302
3303 """
-> 3304 return self.pool.connect()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
442 """Return a DBAPI connection from the pool.
443
444 The connection is instrumented such that when its
(...)
447
448 """
--> 449 return _ConnectionFairy._checkout(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
1262 if not fairy:
-> 1263 fairy = _ConnectionRecord.checkout(pool)
1265 if threadconns is not None:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
711 else:
--> 712 rec = pool._do_get()
714 try:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:180, in QueuePool._do_get(self)
179 with util.safe_reraise():
--> 180 self._dec_overflow()
181 raise
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
176 try:
--> 177 return self._create_connection()
178 except:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
673 if connect:
--> 674 self.__connect()
675 self.finalize_callback = deque()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:901, in _ConnectionRecord.__connect(self)
900 with util.safe_reraise():
--> 901 pool.logger.debug("Error on connect(): %s", e)
902 else:
903 # in SQLAlchemy 1.4 the first_connect event is not used by
904 # the engine, so this will usually not be set
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
897 pool.logger.debug("Created new connection %r", connection)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
641 return connection
--> 643 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:617, in DefaultDialect.connect(self, *cargs, **cparams)
615 def connect(self, *cargs, **cparams):
616 # inherits the docstring from interfaces.Dialect.connect
--> 617 return self.loaded_dbapi.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
Cell In[6], line 5
1 query = """SELECT customers, total_spend from sales
2 WHERE YEAR = 2019
3 LIMIT 1000
4 """
----> 5 df = pd.read_sql_query(query, conn)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:468, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
465 if dtype_backend is lib.no_default:
466 dtype_backend = "numpy" # type: ignore[assignment]
--> 468 with pandasSQL_builder(con) as pandas_sql:
469 return pandas_sql.read_query(
470 sql,
471 index_col=index_col,
(...)
477 dtype_backend=dtype_backend,
478 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
829 raise ImportError("Using URI string without sqlalchemy installed.")
831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832 return SQLDatabase(con, schema, need_transaction)
834 warnings.warn(
835 "pandas only supports SQLAlchemy connectable (engine/connection) or "
836 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
(...)
839 stacklevel=find_stack_level(),
840 )
841 return SQLiteDatabase(con)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pandas/io/sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
1537 self.exit_stack.callback(con.dispose)
1538 if isinstance(con, Engine):
-> 1539 con = self.exit_stack.enter_context(con.connect())
1540 if need_transaction and not con.in_transaction():
1541 self.exit_stack.enter_context(con.begin())
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3280, in Engine.connect(self)
3257 def connect(self) -> Connection:
3258 """Return a new :class:`_engine.Connection` object.
3259
3260 The :class:`_engine.Connection` acts as a Python context manager, so
(...)
3277
3278 """
-> 3280 return self._connection_cls(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:148, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
--> 148 Connection._handle_dbapi_exception_noconnection(
149 err, dialect, engine
150 )
151 raise
152 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2444, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping)
2442 elif should_wrap:
2443 assert sqlalchemy_exception is not None
-> 2444 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
2445 else:
2446 assert exc_info[1] is not None
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:146, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
144 if connection is None:
145 try:
--> 146 self._dbapi_connection = engine.raw_connection()
147 except dialect.loaded_dbapi.Error as err:
148 Connection._handle_dbapi_exception_noconnection(
149 err, dialect, engine
150 )
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3304, in Engine.raw_connection(self)
3282 def raw_connection(self) -> PoolProxiedConnection:
3283 """Return a "raw" DBAPI connection from the connection pool.
3284
3285 The returned object is a proxied version of the DBAPI
(...)
3302
3303 """
-> 3304 return self.pool.connect()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:449, in Pool.connect(self)
441 def connect(self) -> PoolProxiedConnection:
442 """Return a DBAPI connection from the pool.
443
444 The connection is instrumented such that when its
(...)
447
448 """
--> 449 return _ConnectionFairy._checkout(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:1263, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
1255 @classmethod
1256 def _checkout(
1257 cls,
(...)
1260 fairy: Optional[_ConnectionFairy] = None,
1261 ) -> _ConnectionFairy:
1262 if not fairy:
-> 1263 fairy = _ConnectionRecord.checkout(pool)
1265 if threadconns is not None:
1266 threadconns.current = weakref.ref(fairy)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:712, in _ConnectionRecord.checkout(cls, pool)
710 rec = cast(_ConnectionRecord, pool._do_get())
711 else:
--> 712 rec = pool._do_get()
714 try:
715 dbapi_connection = rec.get_connection()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:180, in QueuePool._do_get(self)
178 except:
179 with util.safe_reraise():
--> 180 self._dec_overflow()
181 raise
182 else:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/impl.py:177, in QueuePool._do_get(self)
175 if self._inc_overflow():
176 try:
--> 177 return self._create_connection()
178 except:
179 with util.safe_reraise():
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:390, in Pool._create_connection(self)
387 def _create_connection(self) -> ConnectionPoolEntry:
388 """Called by subclasses to create a new ConnectionRecord."""
--> 390 return _ConnectionRecord(self)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:674, in _ConnectionRecord.__init__(self, pool, connect)
672 self.__pool = pool
673 if connect:
--> 674 self.__connect()
675 self.finalize_callback = deque()
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:901, in _ConnectionRecord.__connect(self)
899 except BaseException as e:
900 with util.safe_reraise():
--> 901 pool.logger.debug("Error on connect(): %s", e)
902 else:
903 # in SQLAlchemy 1.4 the first_connect event is not used by
904 # the engine, so this will usually not be set
905 if pool.dispatch.first_connect:
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
144 assert exc_value is not None
145 self._exc_info = None # remove potential circular references
--> 146 raise exc_value.with_traceback(exc_tb)
147 else:
148 self._exc_info = None # remove potential circular references
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/pool/base.py:896, in _ConnectionRecord.__connect(self)
894 try:
895 self.starttime = time.time()
--> 896 self.dbapi_connection = connection = pool._invoke_creator(self)
897 pool.logger.debug("Created new connection %r", connection)
898 self.fresh = True
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/create.py:643, in create_engine.<locals>.connect(connection_record)
640 if connection is not None:
641 return connection
--> 643 return dialect.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/sqlalchemy/engine/default.py:617, in DefaultDialect.connect(self, *cargs, **cparams)
615 def connect(self, *cargs, **cparams):
616 # inherits the docstring from interfaces.Dialect.connect
--> 617 return self.loaded_dbapi.connect(*cargs, **cparams)
File /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/psycopg2/__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
119 kwasync['async_'] = kwargs.pop('async_')
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)
Exercise#
Try out one of the free online SQL playgrounds